package com.zhongyu.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zhongyu.pojo.*;
import com.zhongyu.vo.XsOrderVo;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.time.Year;
import java.util.List;

/***
 * 销售统计表
 */
public interface XSreportMapper  {
    @Select("SELECT gt.goods_Tpname as goodsTpname,SUM(od.MONEY) as money FROM `smbms_goods_type` gt,`smbms_goods` g,`smbms_order` o,`smbms_order_detail` od\n" +
            "WHERE gt.id=g.goods_typeid AND o.id=od.order_id AND od.goods_id=g.id\n" +
            "AND o.type='2' and o.CREATE_TIME>=#{startDate} and o.CREATE_TIME <=#{endDate} \n" +
            "GROUP BY gt.goods_Tpname")
    //and o.CREATE_TIME >= #{startDate} and o.CREATE_TIME <= #{endDate}
    List<Xsreport> findOrderReport(LocalDateTime startDate , LocalDateTime endDate);
    @Select("SELECT gt.goods_Tpname as goodsTpname,SUM(od.MONEY) as money FROM `smbms_goods_type` gt,`smbms_goods` g,`smbms_order` o,`smbms_order_detail` od\n" +
            "WHERE gt.id=g.goods_typeid AND o.id=od.order_id AND od.goods_id=g.id\n" +
            "AND o.type='2'\n" +
            "GROUP BY gt.goods_Tpname")
    List<Xsreport> findOrderReport1();
    @Select("select * from smbms_xsreport ")
    List<Xsreport> findAll();
    @Select("SELECT MONTH(o.`CREATE_TIME`)AS `month`,SUM(od.`MONEY`) AS `money`\n" +
            "FROM `smbms_order_detail` od, `smbms_order` o\n" +
            "WHERE od.`order_id`=o.`id`    AND o.`TYPE`='2' AND YEAR(o.`CREATE_TIME`)=#{year}\n" +
            "GROUP BY MONTH(o.`CREATE_TIME`) ")
    List<XsView> findAllbyYear(int year);
}
